use -- the proper database below according to the current environment
    Events_Temp 
 -- Test_Events_Temp
 -- Live_Events_Temp

GO
/****** Object:  StoredProcedure [dbo].[spGetEventIndicatorsByCompanyIds2]    Script Date: 12/20/2017 4:40:28 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


----- <PublishedEventIndicators> -----

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_PublishedEventIndicators
	(
	EventIndicatorId uniqueidentifier NOT NULL,
	EventIndicatorCode nvarchar(50) NOT NULL,
	EventIndicatorName nvarchar(450) NOT NULL,
	EventIndicatorSortKey int NOT NULL,
	BaseEventIndicatorId uniqueidentifier NOT NULL,
	CompanyId uniqueidentifier NOT NULL,
	ControversyIndicatorId uniqueidentifier NOT NULL,
	ControversyIndicatorName nvarchar(450) NOT NULL,
	ControversyIndicatorNumber nvarchar(20) NOT NULL,
	ControversyIndicatorSortKey int NOT NULL,
	ProvidesControversy bit NOT NULL,
	ControversySummary nvarchar(1500) NULL,
	AnswerCategoryId int NOT NULL,
	AnswerCategoryText nvarchar(450) NOT NULL,
	AnswerCategoryScore int NOT NULL,
	AnswerCategoryJustification nvarchar(1500) NULL,
	ImpactScore float(53) NOT NULL,
	ImpactText nvarchar(300) NULL,
	ImpactCommentsId uniqueidentifier NULL,
	RiskScore float(53) NOT NULL,
	RiskText nvarchar(300) NULL,
	RiskCommentsId uniqueidentifier NULL,
	PreparednessScore int NULL,
	PreparednessText nvarchar(300) NULL,
	PreparednessCommentsId uniqueidentifier NULL,
	OutlookScore int NULL,
	OutlookText nvarchar(300) NULL,
	OutlookCommentsId uniqueidentifier NULL,
	Timestamp datetime NOT NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_PublishedEventIndicators SET (LOCK_ESCALATION = TABLE)
GO
ALTER TABLE dbo.Tmp_PublishedEventIndicators ADD CONSTRAINT
	DF_PublishedEventIndicators_EventIndicatorCode DEFAULT '' FOR EventIndicatorCode
GO
IF EXISTS(SELECT * FROM dbo.PublishedEventIndicators)
	 EXEC('INSERT INTO dbo.Tmp_PublishedEventIndicators (EventIndicatorId, EventIndicatorName, EventIndicatorSortKey, BaseEventIndicatorId, CompanyId, ControversyIndicatorId, ControversyIndicatorName, ControversyIndicatorNumber, ControversyIndicatorSortKey, ProvidesControversy, ControversySummary, AnswerCategoryId, AnswerCategoryText, AnswerCategoryScore, AnswerCategoryJustification, ImpactScore, ImpactText, ImpactCommentsId, RiskScore, RiskText, RiskCommentsId, PreparednessScore, PreparednessText, PreparednessCommentsId, OutlookScore, OutlookText, OutlookCommentsId, Timestamp)
		SELECT EventIndicatorId, EventIndicatorName, EventIndicatorSortKey, BaseEventIndicatorId, CompanyId, ControversyIndicatorId, ControversyIndicatorName, ControversyIndicatorNumber, ControversyIndicatorSortKey, ProvidesControversy, ControversySummary, AnswerCategoryId, AnswerCategoryText, AnswerCategoryScore, AnswerCategoryJustification, ImpactScore, ImpactText, ImpactCommentsId, RiskScore, RiskText, RiskCommentsId, PreparednessScore, PreparednessText, PreparednessCommentsId, OutlookScore, OutlookText, OutlookCommentsId, Timestamp FROM dbo.PublishedEventIndicators WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.PublishedEventIndicators
GO
EXECUTE sp_rename N'dbo.Tmp_PublishedEventIndicators', N'PublishedEventIndicators', 'OBJECT' 
GO
COMMIT
GO

update PublishedEventIndicators set EventIndicatorCode = eit.Number
	from PublishedEventIndicators pei join EventIndicatorTemplates eit on pei.BaseEventIndicatorId = eit.Id

update PublishedEventIndicators set ControversySummary = null, AnswerCategoryJustification = null, OutlookScore = null, OutlookText = null, OutlookCommentsId = null 
	where AnswerCategoryId < 3

update PublishedEventIndicators set ImpactText = null, ImpactCommentsId = null, RiskText = null, RiskCommentsId = null, PreparednessScore = null, PreparednessText = null, PreparednessCommentsId = null
	where AnswerCategoryId < 4

GO

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.PublishedEventIndicators
	DROP CONSTRAINT DF_PublishedEventIndicators_EventIndicatorCode
GO
ALTER TABLE dbo.PublishedEventIndicators SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
GO

ALTER PROCEDURE [dbo].[spGetEventIndicatorsByCompanyIds]
	@CompanyIds NVARCHAR(MAX)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @XML XML
	SET @XML = CONVERT(XML, @CompanyIds, 1)

	DECLARE @SelectedCompanies TABLE
	(
		Id uniqueidentifier
	)
	INSERT INTO @SelectedCompanies
	SELECT TID.id.value('.','uniqueidentifier') AS Id
	FROM @XML.nodes('/CompanyIds') AS T(c) outer apply T.c.nodes('Id') as TID(id)

	SELECT [EventIndicatorId]
		  ,[EventIndicatorCode]
		  ,[EventIndicatorName]		  
		  ,[BaseEventIndicatorId]
		  ,[CompanyId]
		  ,[ControversyIndicatorId]
		  ,[ControversyIndicatorName]
		  ,[ControversyIndicatorNumber]
		  ,[ControversyIndicatorSortKey]
		  ,[ProvidesControversy]
		  ,[ControversySummary]
		  ,[AnswerCategoryId]
		  ,[AnswerCategoryText]
		  ,[AnswerCategoryScore]
		  ,[AnswerCategoryJustification]
		  ,[ImpactScore]
		  ,[ImpactText]
		  ,[ImpactCommentsId]
		  ,[RiskScore]
		  ,[RiskText]
		  ,[RiskCommentsId]
		  ,[PreparednessScore]
		  ,[PreparednessText]
		  ,[PreparednessCommentsId]
		  ,[OutlookScore]
		  ,[OutlookText]
		  ,[OutlookCommentsId]
	FROM [dbo].[PublishedEventIndicators] AS PEI
	INNER JOIN @SelectedCompanies AS SelectedCompanies ON SelectedCompanies.Id = PEI.CompanyId
	 
END

GO

----- </PublishedEventIndicators> -----

----- <PublishedEventIndicatorComments> -----

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.PublishedEventIndicatorComments
	DROP COLUMN CommentType
GO
ALTER TABLE dbo.PublishedEventIndicatorComments SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
GO

ALTER PROCEDURE [dbo].[spGetEventIndicatorCommentsByCompanyIds]
	@CompanyIds NVARCHAR(MAX)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @XML XML
	SET @XML = CONVERT(XML, @CompanyIds, 1)

	DECLARE @SelectedCompanies TABLE
	(
		Id uniqueidentifier
	)
	INSERT INTO @SelectedCompanies
	SELECT TID.id.value('.','uniqueidentifier') AS Id
	FROM @XML.nodes('/CompanyIds') AS T(c) outer apply T.c.nodes('Id') as TID(id)

	SELECT [EventIndicatorId]
		  ,[CommentId]
		  ,[Comment]
	FROM [dbo].[PublishedEventIndicatorComments] AS PublishedEventIndicatorComments
	INNER JOIN @SelectedCompanies AS SelectedCompanies ON SelectedCompanies.Id = PublishedEventIndicatorComments.CompanyId
	 
END

GO

delete PublishedEventIndicatorComments
	from PublishedEventIndicatorComments peic 
	left join PublishedEventIndicators pei on peic.EventIndicatorId = pei.EventIndicatorId 
			and (peic.CommentId = pei.OutlookCommentsId or peic.CommentId = pei.ImpactCommentsId or peic.CommentId = pei.RiskCommentsId or peic.CommentId = pei.PreparednessCommentsId)
	where pei.EventIndicatorId is null

----- </PublishedEventIndicatorComments> -----